package com.bbu.javaexam.controller;

import com.bbu.javaexam.domain.ClassStudent;
import com.bbu.javaexam.domain.Student;
import com.bbu.javaexam.service.ClassStudentService;
import com.bbu.javaexam.service.StudentService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;

/**
 * @author Alex
 * @version 1.0.0
 * @ClassName importExcel.java
 * @Description TODO
 * @createTime 2022年02月21日 15:33:00
 */
@RestController
@Slf4j
@RequestMapping("/excel")
public class ImportExcel {

    @Autowired
    private StudentService studentService;

    @Autowired
    private ClassStudentService classStudentService;

    @PostMapping("/upload/{id}")
    public void importExcel(MultipartFile file, @PathVariable Long id) throws Exception {
        InputStream inputStream = file.getInputStream();

        //07年的 不兼容之前
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);

        XSSFSheet sheet = xssfWorkbook.getSheetAt(0);

        //获取行数
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i <= lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            Student student = new Student();
            if (row.getCell(0) != null) {
                String stringCellValue = row.getCell(0).getStringCellValue();
                student.setStudentId(Long.parseLong(stringCellValue));
            }
            if (row.getCell(1) != null) {
                student.setStudentName(row.getCell(1).getStringCellValue());
            }
            if (row.getCell(2) != null) {
                row.getCell(2).setCellType(CellType.STRING);
                student.setGrade(row.getCell(2).getStringCellValue());
            }
            if (row.getCell(3) != null) {
                row.getCell(3).setCellType(CellType.STRING);
                student.setMajor(row.getCell(3).getStringCellValue());
            }
            if (row.getCell(4) != null) {
                row.getCell(4).setCellType(CellType.STRING);
                student.setClazz(row.getCell(4).getStringCellValue());
            }
            if (row.getCell(5) != null) {
                row.getCell(5).setCellType(CellType.STRING);
                student.setInstitute(row.getCell(5).getStringCellValue());
            }
            if (row.getCell(6) != null) {
                row.getCell(6).setCellType(CellType.STRING);
                student.setTel(row.getCell(6).getStringCellValue());
            }
            if (row.getCell(7) != null) {
                row.getCell(7).setCellType(CellType.STRING);
                student.setEmail(row.getCell(7).getStringCellValue());
            }
            if (row.getCell(8) != null) {
                row.getCell(8).setCellType(CellType.STRING);
                student.setPwd(row.getCell(8).getStringCellValue());
            }
            if (row.getCell(9) != null) {
                row.getCell(9).setCellType(CellType.STRING);
                student.setCardId(row.getCell(9).getStringCellValue());
            }
            if (row.getCell(10) != null) {
                row.getCell(10).setCellType(CellType.STRING);
                student.setSex(row.getCell(10).getStringCellValue());
            }
            if (row.getCell(11) != null) {
                row.getCell(11).setCellType(CellType.STRING);
                student.setRole(row.getCell(11).getStringCellValue());
            }
            studentService.add(student);
            ClassStudent classStudent = new ClassStudent();
            classStudent.setClassid(id);
            classStudent.setStudentid(student.getStudentId());
            classStudentService.save(classStudent);
        }
    }

    @GetMapping("/download")
    public void testCreateSheet(HttpServletResponse response) throws Exception {
        // 【SXSSFWorkbook----------->sheet----------->row------------>cell】
        // SXSSFWorkbook支持excel2010
        SXSSFWorkbook sw = new SXSSFWorkbook();
        // 表名
        SXSSFSheet sheet = sw.createSheet("学生表");
        // 创建行 【第一行  从零开始】
        SXSSFRow row = sheet.createRow(0);
        // 创建列 【第一列  从零开始】
        SXSSFCell cell = row.createCell(0);

        // 创建样式
        CellStyle cellStyle = sw.createCellStyle();

        // 设置单元格的横向和纵向对齐方式
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 给指定列设置样式
        cell.setCellStyle(cellStyle);

        SXSSFRow row1 = sheet.createRow(0);
        row1.createCell(0).setCellValue("学号");
        row1.createCell(1).setCellValue("姓名");
        row1.createCell(2).setCellValue("年级");
        row1.createCell(3).setCellValue("专业");
        row1.createCell(4).setCellValue("班级");
        row1.createCell(5).setCellValue("学院");
        row1.createCell(6).setCellValue("电话");
        row1.createCell(7).setCellValue("邮箱");
        row1.createCell(8).setCellValue("密码");
        row1.createCell(9).setCellValue("身份ID");
        row1.createCell(10).setCellValue("性别");
        row1.createCell(11).setCellValue("角色");


        SXSSFRow row2 = sheet.createRow(1);
        row2.createCell(0).setCellValue("82040321040");
        row2.createCell(1).setCellValue("模板1");
        row2.createCell(2).setCellValue("2020");
        row2.createCell(3).setCellValue("软件工程");
        row2.createCell(4).setCellValue("20软件工程1班");
        row2.createCell(5).setCellValue("软件工程学院");
        row2.createCell(6).setCellValue("132xxxxxxxx");
        row2.createCell(7).setCellValue("1152240078@qq.com");
        row2.createCell(8).setCellValue("123456");
        row2.createCell(9).setCellValue("123456");
        row2.createCell(10).setCellValue("男");
        row2.createCell(11).setCellValue("2");
        // 新建导出流
        response.setHeader("Content-Disposition", "attachment;filename=template.xlsx");
        //response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        OutputStream outputStream = response.getOutputStream();
        sw.write(outputStream);
        sw.close();
    }

}
